﻿/*
 * 文件名：JobInfo.cs
 * 类名：XHCTMS.DAL.JobInfo
 * 创建人：仇祖萍
 * 创建时间：2013-05-20
 * 备注：
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XHCTMS.DAL
{
    #region 存储过程

//CREATE PROC p_jobINFO
//@jobID INT=NULL,
//@jobname   VARCHAR(32)=NULL,
//@jobdesc   VARCHAR(64)=NULL,
//@jobflag bit=null
            
//AS
//BEGIN
//SET NOCOUNT ON
//SELECT * FROM     
//Job
           
//WHERE (@jobID IS NULL OR job_ID=@jobID)
//    AND	(@jobname IS NULL OR job_name=@jobname)
//   AND	(@jobdesc IS NULL OR job_desc=@jobdesc) 
//   and (@jobflag IS NULL OR job_flag=@jobflag) 
//END

// EXEC p_jobINFO NULL,NULL,NULL,1

    #endregion

    /// <summary>
    /// 对职位信息进行增删改查操作
    /// </summary>
    public class JobInfo
    {
        /// <summary>
        /// 增加职位信息
        /// </summary>
        /// <param name="Job">职位信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int AddJobInfo(MODEL.JobInfo job)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append("insert into job values(@jobName,@jobDesc,default);");

            SqlParameter[] paras = {   
                                      new SqlParameter("@jobName",SqlDbType.NVarChar,32),
                                      new SqlParameter("@jobDesc",SqlDbType.NVarChar,64)  
                                  };

            paras[0].Value = job.JobName;
            paras[1].Value = job.JobDesc;

            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 删除职位信息
        /// </summary>
        /// <param name="jobFlag"></param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int DeleteJobInfo(int jobId)
        {
            string queryString = "delete from job where job_id=@jobId";

            SqlParameter[] Para = {
                                      new SqlParameter("@jobId", SqlDbType.Int)
                                  };

            Para[0].Value = jobId;

            return new DBHELP.Common().ExcuteProcQuery(queryString, Para);
        }

        /// <summary>
        /// 修改职位信息
        /// </summary>
        /// <param name="Job">职位信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Update(MODEL.JobInfo job)
        {
            string queryString = "update job set job_id=@jobId,job_name=@jobName,job_desc=@jobdesc,job_flag=@jobFlag where job_id=@jobId ";

            SqlParameter[] Para = {
                                      new SqlParameter("@jobId", SqlDbType.Int),
                                      new SqlParameter("@jobName",SqlDbType.NVarChar,32),
                                      new SqlParameter("@jobDesc",SqlDbType.NVarChar,64),
                                      new SqlParameter("@jobFlag",SqlDbType.Bit)

                                  };
            Para[0].Value = job.JobId;
            Para[1].Value = job.JobName;
            Para[2].Value = job.JobDesc;
            Para[3].Value = job.JobFlag;

            return new DBHELP.Common().ExcuteQuery(queryString, Para);
        }

        /// <summary>
        /// 查询所有职位信息
        /// </summary>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable ViewJobIsusing()
        {
            string queryString = "select job_id,job_name,job_desc,job_flag from job where job_flag=1";

            return new DBHELP.Common().GetDataTable(queryString);
        }

        public DataTable ViewById(MODEL.JobInfo job)
        {
            string queryString = "select job_id,job_name,job_desc,job_flag from job where job_id=@jobId";
            SqlParameter[] paras ={
                                     new SqlParameter("@jobId",SqlDbType.Int)
                                 };

            paras[0].Value = job.JobId;

            return new DBHELP.Common().GetDataTable(queryString.ToString(), paras);
        }


        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="job"></param>
        /// <returns></returns>
        public DataTable ViewBy(MODEL.JobInfo job)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("p_jobINFO");
            SqlParameter[] paras = {
                                       new SqlParameter("@jobId", SqlDbType.Int),
                                       new SqlParameter("@jobName", SqlDbType.VarChar,32),
                                       new SqlParameter("@jobDesc", SqlDbType.VarChar,64),
                                       new SqlParameter("@jobFlag", SqlDbType.Bit)

                                   };
            if (job.JobId != null)
            {
                paras[0].Value = job.JobId;
            }
            else
            {
                paras[0].Value = null;
            }
            if (job.JobName != null)
            {
                paras[1].Value = job.JobName;
            }
            else
            {
                paras[1].Value = null;
            }
            if (job.JobDesc != null)
            {
                paras[2].Value = job.JobDesc;
            }
            else
            {
                paras[2].Value = null;
            }
            if (job.JobFlag != null)
            {
                paras[3].Value = job.JobFlag;
            }
            else
            {
                paras[3].Value = null;
            }

            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="jobId"></param>
        /// <param name="job"></param>
        /// <returns></returns>
        public int AlterFlag(int jobId,int IsUsing)
        {
  
            StringBuilder queryString = new StringBuilder();
            if (IsUsing == 1)
            {
                queryString.Append(@"update job set job_flag=@IsUsing where job_id=@jobId");
            }
            if (IsUsing == 0)
            {
                queryString.Append("update job set job_flag=@IsUsing where job_id=@jobId");
            }
           
            
            SqlParameter[] paras = {
                                       new SqlParameter("@IsUsing", SqlDbType.Int),
                                       new SqlParameter("@jobId", SqlDbType.Int)
                                   };
            paras[0].Value = IsUsing;
            paras[1].Value = jobId;
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
           
          
        }

        public int UpdateById(MODEL.JobInfo job)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("update job set job_name=@jobName,job_desc=@jobDesc,job_flag=@jobFlag where job_id=@jobId and job_flag=1");
            SqlParameter[] para ={
                                     new SqlParameter("@jobId", SqlDbType.Int),
                                       new SqlParameter("@jobName", SqlDbType.VarChar,32),
                                       new SqlParameter("@jobDesc", SqlDbType.VarChar,64),
                                       new SqlParameter("@jobFlag", SqlDbType.Bit)
                                };
            para[0].Value = job.JobId;
            para[1].Value = job.JobName;
            para[2].Value = job.JobDesc;
            para[3].Value = job.JobFlag;

            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

    }
}
